package com.gopay.checkorder.service.task.query;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.RandomAccessFile;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.mail.MessagingException;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.gopay.checkorder.service.BcdlOrderSendBankService;
import com.gopay.checkorder.service.task.Task;
import com.gopay.common.constants.bank.BankConstants;
import com.gopay.common.constants.bank.BankOrderType;
import com.gopay.common.constants.bank.BisBankBusinessProd;
import com.gopay.common.constants.proccode.ProcCodeConstants;
import com.gopay.common.domain.bank.BcdlMessage;
import com.gopay.common.domain.bank.BcdlResult;
import com.gopay.common.domain.bank.icbc.bcdl.pay.IcbcMessageData;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.common.util.DateUtils;
import com.gopay.common.util.SendMail;
import com.gopay.common.util.StringTools;
import com.gopay.common.util.spring.SpringContextUtil;

/**
 * 工行银企下载网银明细任务
 * 
 * @IcbcQryAcctDetailTaskImpl.java
 * @author zhengxue
 * @2017-6-29 上午11:24:55 www.gopay.com.cn Inc.All rights reserved.
 */
public class IcbcQryAcctDetailTaskImpl implements Task {
    private final static Logger logger = LoggerFactory.getLogger("CHECK_ORDER");
    @Resource(name = "acctDetailConfig")
    private Map<String, String> acctDetailConfig;
    @Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;

    private int MAX_QUERY_SIZE = 1000; // 最大查询次数
    private int COUNT_SIZE = 100; // 包内最大订单数
    private String filePath = "/usr/local/ciecc/webapps/upload/ICBC/transDetail/";

    @Override
    public void doTask() {
        logger.error("IcbcQryAcctDetailTaskImpl: 定时任务start ");
        try {
            if ("off".equals(acctDetailConfig.get("mailSwitch"))) {
                logger.error("IcbcQryAcctDetailTaskImpl: 不进行查询操作直接返回!");
                return;
            }
            // 当前时间
            Date curDate = new Date();
            String today = DateUtils.format(curDate);
            String yesterday = DateUtils.getYesterday();

            int tQuerySize = getBcdlOrderCount(today.substring(0, 8)); // 今日订单查询数
            int yQuerySize = getBcdlOrderCount(yesterday); // 昨日订单查询数

            List<String> fileNames = new ArrayList<String>(); // 网银明细文件
            if (curDate.getHours() < 9) {
                // 下载前一天网银明细
                getAcctDetail(yesterday, yQuerySize + MAX_QUERY_SIZE);
                fileNames.add("ICBC_BCDL_DETAILS_" + yesterday + ".xlsx");
            } else if (curDate.getHours() < 10) {
                // 下载当天网银明细
                getAcctDetail(today, tQuerySize + MAX_QUERY_SIZE);
                fileNames.add("ICBC_BCDL_DETAILS_" + today + ".xlsx");
            } else if (curDate.getHours() < 16) {
                // 下载当天网银明细
                getAcctDetail(today, tQuerySize + MAX_QUERY_SIZE);
                fileNames.add("ICBC_BCDL_DETAILS_" + today + ".xlsx");
            }
            // 发送邮件
            sendMail(fileNames);
        } catch (Exception e) {
            logger.error("IcbcQryAcctDetailTaskImpl error: ", e);
        }

    }

    public void getAcctDetail(String fileTime, int querySize) throws Exception {
        String beanName = BankConstants.BANK_LOGO_ICBC + "-BCDL";
        BcdlOrderSendBankService sendBankService = (BcdlOrderSendBankService) SpringContextUtil.getBean(beanName);
        String nextTag = ""; // 网银明细查询下页标识
        String icbcFilePath = filePath + "txt/ICBC_BCDL_DETAILS_" + fileTime + ".txt";
        File file = new File(icbcFilePath);

        // 判断查询文件是否已存在
        if (file.exists() && file.length() > 0) {
            String lastLine = readLastLine(file, "UTF-8");
            if (lastLine.trim().equals("########")) {
                // 网银明细已下载
                logger.error("IcbcQryAcctDetailTaskImpl " + file.getName() + " is download");
                parseFile(file, fileTime);
                return;
            }
        }

        // 循环发起查询请求
        for (int i = 0; i < querySize; i++) {
            logger.error("IcbcQryAcctDetailTaskImpl sendQuery start size: " + i + " nextTag: " + nextTag);
            BcdlMessage req = new BcdlMessage();
            req.setBank(BankConstants.BANK_LOGO_ICBC);
            req.setOrderDate(fileTime);
            req.setSpecial(nextTag); // 查询下页标识

            BcdlResult result = sendBankService.queryAcctDetail(req);
            if (!ProcCodeConstants.PROC_CODE_100S1000.equals(result.getResultCode())) {
                logger.error("IcbcQryAcctDetailTaskImpl getAcctDetail error errorCode: " + result.getResultCode()
                        + "errorMsg: " + result.getErrorMsg());

                // 查询失败获取上次结果下标继续查询
                String lastLine = readLastLine(file, "UTF-8");
                if (file.exists() && file.length() > 0) {
                    if (lastLine.trim().equals("########")) {
                        // 网银明细已下载
                        logger.error("IcbcQryAcctDetailTaskImpl " + file.getName() + " is download");
                        parseFile(file, fileTime);
                        break;
                    } else {
                        // 查询下页标识
                        nextTag = getNextTag(icbcFilePath);
                    }
                }
                continue;
            }
            // 查询成功
            IcbcMessageData detailData = (IcbcMessageData) result.getModel("acctDetail");
            if (StringUtils.isNotEmpty(detailData.getNextTag())) {
                // 银行返回有下页标识继续查询
                nextTag = detailData.getNextTag();
            } else {
                // 银行返回无下页标识跳出循环
                logger.error("IcbcQryAcctDetailTaskImpl sendQuery end");
                parseFile(file, fileTime);
                break;
            }
        }
    }

    public void parseFile(File file, String fileTime) {
        logger.error("IcbcQryAcctDetailTaskImpl parseFile start: ");
        try {
            String xmlPlain = "";
            String plainMessage = "";
            int insertSize = 0;
            StringBuffer sb = new StringBuffer();
            BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file));
            BufferedReader reader = new BufferedReader(new InputStreamReader(fis, "UTF-8"), 5 * 1024 * 1024);
            while ((xmlPlain = reader.readLine()) != null) {
                sb.append(xmlPlain);
                if ("</CMS>".equals(xmlPlain)) {
                    // 一次请求报文数据
                    plainMessage = sb.toString();
                    sb.setLength(0); // 清空StringBuffer
                    ++insertSize;
                    creatExcel(fileTime, getDetailData(plainMessage), insertSize);
                }
            }
            reader.close();
            fis.close();
            logger.error("IcbcQryAcctDetailTaskImpl parseFile end");
        } catch (Exception e) {
            logger.error("IcbcQryAcctDetailTaskImpl parseFile error: ", e);
        }
    }

    public List<IcbcMessageData> getDetailData(String detailXml) throws DocumentException {
        List<IcbcMessageData> details = new ArrayList<IcbcMessageData>();
        Document document = DocumentHelper.parseText(detailXml);
        Element out = (Element) document.selectSingleNode("//CMS/eb/out");

        List resRd = document.selectNodes("/CMS/eb/out/rd");
        Iterator rds = resRd.iterator();
        while (rds.hasNext()) {
            Element rd = (Element) rds.next();
            IcbcMessageData detail = new IcbcMessageData();
            detail.setCrvouhNo(rd.elementText("VouhNo"));
            detail.setPayAccNo(out.elementText("AccNo"));
            detail.setRecAccNo(rd.elementText("RecipAccNo"));
            detail.setRecAccNameCN(rd.elementText("RecipName"));
            // 格式化日期
            String time = rd.elementText("Time");
            String sdf = new String("yyyy-MM-dd-HH.mm.ss");
            String sdf1 = new String("yyyy-MM-dd HH:mm:ss");
            Date date = DateUtils.strToDate(time, sdf);
            String transTime = DateUtils.format(date, sdf1);
            detail.setTransTime(transTime);
            detail.setDcblType(Integer.parseInt(rd.elementText("Drcrf")));
            BigDecimal amount = new BigDecimal("0");
            if (1 == detail.getDcblType()) {
                amount = new BigDecimal(rd.elementText("DebitAmount"));
            } else if (2 == detail.getDcblType()) {
                amount = new BigDecimal(rd.elementText("CreditAmount"));
            }
            detail.setPayAmt(amount.movePointLeft(2).toString());
            detail.setRecBankNo(rd.elementText("RecipBkNo1"));
            detail.setSummary(rd.elementText("Summary"));
            detail.setUseCN(rd.elementText("UseCN"));
            detail.setPostScript(rd.elementText("PostScript"));
            detail.setBalance(rd.elementText("Balance"));
            detail.setReceiptInfo(rd.elementText("ReceiptInfo"));
            details.add(detail);
        }
        return details;
    }

    public void creatExcel(String fileTime, List<IcbcMessageData> detailData, int insertSize) throws IOException {
        logger.error("IcbcQryAcctDetailTaskImpl creatExcel start: " + insertSize);
        if (detailData == null || detailData.size() <= 0) {
            return;
        }

        File file = new File(filePath + "xls/ICBC_BCDL_DETAILS_" + fileTime + ".xlsx");
        if (!file.exists()) {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet("ICBC_BCDL_DETAILS_" + fileTime);

            XSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // 创建HSSFFont实例，设置字体、字号、加粗、颜色
            XSSFFont font = wb.createFont();
            font.setFontName(HSSFFont.FONT_ARIAL);// 字体
            font.setFontHeightInPoints((short) 10);// 字号
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
            font.setColor(HSSFColor.BLACK.index);// 颜色
            style.setFont(font);

            String[] titles = new String[] { "凭证号", "本方账号", "对方账号", "交易时间", "借/贷", "借方发生额", "贷方发生额", "对方行号", "摘要",
                    "用途", "对方单位名称", "余额", "个性化信息" };
            XSSFRow firstRow = sheet.createRow(0);
            XSSFRow titleRow = sheet.createRow(1);
            
            XSSFCell firstCell = firstRow.createCell(0);
            firstCell.setCellValue("[HISTORYDETAIL]");

            for (int i = 0; i < titles.length; i++) {
                XSSFCell cell = titleRow.createCell(i);
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(new XSSFRichTextString(titles[i]));
                cell.setCellStyle(style);
            }
            sheet.setDefaultColumnWidth((short) 25);

            ByteArrayOutputStream os = new ByteArrayOutputStream();
            wb.write(os);
            byte[] xlsx = os.toByteArray();
            OutputStream out = new FileOutputStream(file);
            out.write(xlsx);
            out.flush();
            out.close();
            os.close();
            logger.error("IcbcQryAcctDetailTaskImpl creatExcelTitles end: " + insertSize);
        }

        FileInputStream fs = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sheet1 = wb.getSheetAt(0);
        // 总行数
        int lastRowNo = sheet1.getLastRowNum() + 1;

        for (int i = 0; i < detailData.size(); i++) {
            IcbcMessageData data = detailData.get(i);
            XSSFRow tmprow = sheet1.createRow(i + lastRowNo);
            XSSFCell tmpcell0 = tmprow.createCell(0);
            tmpcell0.setCellValue(data.getCrvouhNo());
            
            XSSFCell tmpcell1 = tmprow.createCell(1);
            tmpcell1.setCellValue(data.getPayAccNo());

            XSSFCell tmpcell2 = tmprow.createCell(2);
            tmpcell2.setCellValue(data.getRecAccNo());

            XSSFCell tmpcell3 = tmprow.createCell(3);
            tmpcell3.setCellValue(data.getTransTime());

            XSSFCell tmpcell4 = tmprow.createCell(4);
            if (1 == data.getDcblType()) {
                tmpcell4.setCellValue("借");
            } else if (2 == data.getDcblType()) {
                tmpcell4.setCellValue("贷");
            }

            XSSFCell tmpcell5 = tmprow.createCell(5);
            if (1 == data.getDcblType()) {
                tmpcell5.setCellValue(data.getPayAmt());
            }

            XSSFCell tmpcell6 = tmprow.createCell(6);
            if (2 == data.getDcblType()) {
                tmpcell6.setCellValue(data.getPayAmt());
            }

            XSSFCell tmpcell7 = tmprow.createCell(7);
            tmpcell7.setCellValue(data.getRecBankNo());

            XSSFCell tmpcell8 = tmprow.createCell(8);
            tmpcell8.setCellValue(data.getSummary());
            
            XSSFCell tmpcell9 = tmprow.createCell(9);
            tmpcell9.setCellValue(data.getUseCN());
            
            XSSFCell tmpcell10 = tmprow.createCell(10);
            tmpcell10.setCellValue(data.getRecAccNameCN());
            
            XSSFCell tmpcell11 = tmprow.createCell(11);
            tmpcell11.setCellValue(data.getBalance());
            
            XSSFCell tmpcell12 = tmprow.createCell(12);
            tmpcell12.setCellValue(data.getReceiptInfo());
        }

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        byte[] xlsx = os.toByteArray();
        OutputStream out = new FileOutputStream(file);
        out.write(xlsx);
        out.flush();
        out.close();
        os.close();
        logger.error("IcbcQryAcctDetailTaskImpl creatExcel end: " + insertSize);
    }

    public void sendMail(List<String> fileNames) {
        List<String> sendFile = new ArrayList<String>();
        // 判断本地服务器是否存在网银明细文件文件
        for (Iterator<String> it = fileNames.iterator(); it.hasNext();) {
            String fileName = it.next();
            if (!(new File(filePath + "xls/" + fileName).exists())) {
                logger.error("IcbcQryAcctDetailTaskImpl sendMail " + fileName + " is not exist");
                it.remove();
                continue;
            }
            sendFile.add(filePath + "xls/" + fileName);
            logger.error("IcbcQryAcctDetailTaskImpl sendFile: " + sendFile);
        }

        logger.error("IcbcQryAcctDetailTaskImpl fileNames: " + fileNames + "fileNames.size: " + fileNames.size()
                + "sendFile.size: " + sendFile.size());

        if (fileNames.size() > 0) {
            String subject = "工行银企直联网银明细文件";
            String messageText = "附件为工行银企直联网银明细文件, 请查收!";
            String[] cc = acctDetailConfig.get("sendmail.cc").split(","); // 获取多个抄送人
            List<String> ccList = Arrays.asList(cc);

            try {
                SendMail.sendMessage(acctDetailConfig.get("sendmail.host"), acctDetailConfig.get("sendmail.sender"),
                        acctDetailConfig.get("sendmail.username"), acctDetailConfig.get("sendmail.password"),
                        acctDetailConfig.get("sendmail.receiver"), subject, messageText, "text/html;charset=gb2312",
                        ccList, sendFile, fileNames);
            } catch (MessagingException e) {
                logger.error("IcbcQryAcctDetailTaskImpl sendMail error: ", e);
            }
        }
    }

    /**
     * 获取网银明细文件最后一行数据
     * 
     * @param file
     * @param charset
     * @return
     * @throws IOException
     */
    public static String readLastLine(File file, String charset) throws IOException {
        if (!file.exists() || file.isDirectory() || !file.canRead()) {
            return null;
        }
        RandomAccessFile raf = null;
        try {
            raf = new RandomAccessFile(file, "r");
            long len = raf.length();
            if (len == 0L) {
                return "";
            } else {
                long pos = len - 1;
                while (pos > 0) {
                    pos--;
                    raf.seek(pos);
                    if (raf.readByte() == '\n') {
                        break;
                    }
                }
                if (pos == 0) {
                    raf.seek(0);
                }
                byte[] bytes = new byte[(int) (len - pos)];
                raf.read(bytes);
                if (charset == null) {
                    return new String(bytes);
                } else {
                    return new String(bytes, charset);
                }
            }
        } catch (FileNotFoundException e) {
        } finally {
            if (raf != null) {
                try {
                    raf.close();
                } catch (Exception e2) {
                }
            }
        }
        return null;
    }

    public String getNextTag(String file) throws IOException {
        String nextTag = "";
        RandomAccessFile rf = null;

        rf = new RandomAccessFile(file, "r");
        long len = rf.length();
        long start = rf.getFilePointer();
        long nextend = start + len - 1;
        String line;
        rf.seek(nextend);
        int c = -1;
        while (nextend > start) {
            c = rf.read();
            if (c == '\n' || c == '\r') {
                line = rf.readLine();
                if (line != null) {
                    if (line.contains("<NextTag>")) {
                        nextTag = line.substring(line.indexOf("<NextTag>") + 9, line.indexOf("</NextTag>"));
                        break;
                    }

                }

                nextend--;
            }
            nextend--;
            rf.seek(nextend);
            // if (nextend == 0) {// 当文件指针退至文件开始处，输出第一行
            // System.out.println("333" + rf.readLine());
            // }
        }
        logger.error("IcbcQryAcctDetailTaskImpl getNextTag: " + nextTag);
        return nextTag;
    }

    /**
     * 查询工行银企付款订单
     * 
     * @param date
     * @return
     * @throws ParseException
     */
    public int getBcdlOrderCount(String date) throws ParseException {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
        Date queryDate = formatter.parse(date); // 取时间
        Calendar calendar = new GregorianCalendar();
        calendar.setTime(queryDate);
        calendar.add(calendar.DATE, 1);
        queryDate = calendar.getTime();
        String endTime = formatter.format(queryDate);
        logger.error("IcbcQryAcctDetailTaskImpl getBcdlOrderCount beginTime: " + date + "endTime: " + endTime);

        StringBuffer sql = new StringBuffer(250);
        sql.append("select b.GOPAY_OUT_ORDER_ID,b.GOPAY_ORDER_ID from BIS_BANK_MAIN_ORDER b ");
        sql.append(" where b.BANK_BIZ_PROD_CODE = :bankBizProdCode ");
        sql.append(" and b.ORDER_TYPE = :orderType ");
        sql.append(" and b.GOPAY_BANK_CODE = :bankCode ");
        sql.append(" and b.SEND_BANK_TM >=to_date(:beginTime ,'yyyymmdd') ");
        sql.append(" and b.SEND_BANK_TM <to_date(:endTime ,'yyyymmdd') ");

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("bankCode", BankConstants.BANK_LOGO_ICBC);
        params.put("beginTime", date);
        params.put("endTime", endTime);
        List<Object[]> list = commonQueryDAO.findBySql(sql.toString(), params);

        List<String> orderIds = new ArrayList<String>();
        for (Object[] bankOrderId : list) {
            orderIds.add(StringTools.checkNulltoString(bankOrderId[0]));
        }
        logger.error("IcbcQryAcctDetailTaskImpl getBcdlOrderCount size: " + orderIds.size());
        int size = orderIds.size() / COUNT_SIZE + ((orderIds.size() % COUNT_SIZE == 0) ? 0 : 1);
        return size;
    }
}
